package com.heh.mybatisplus.controller;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.BufferedWriter;
import java.io.FileOutputStream;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.*;

/**
 * @项目名： spring-emq
 * @包名： com.heh.mybatisplus.controller
 * @文件名: DatabaseToExcelExporter
 * @创建者: liufudong@szyh-smart.com
 * @创建时间: 2024/10/24 19:55
 * @描述：
 */
public class DatabaseToMdExporter {
    // MySQL JDBC URL, Username, Password
    private static final String URL = "jdbc:mysql://localhost:3306/";
    private static final String USER = "root";
    private static final String PASSWORD = "szyh2016";
//edu_luohu

    public static void main(String[] args) {
        exportDatabaseStructureToMarkdown();
    }
    public static void exportDatabaseStructureToMarkdown() {
        // 查询所有表名
        String queryTables = "SELECT table_name FROM information_schema.tables WHERE table_schema = 'edu_luohu'";
        int tableIndex = 1;  // 表的索引

        try (Connection connection = DriverManager.getConnection(URL, USER, PASSWORD);
             PreparedStatement preparedStatement = connection.prepareStatement(queryTables);
             ResultSet resultSet = preparedStatement.executeQuery();
             BufferedWriter writer = new BufferedWriter(new FileWriter("Database_Structure.md"))) {

            while (resultSet.next()) {
                String tableName = resultSet.getString("table_name");
                writer.write("### " + tableIndex + ". " + tableName + "\n\n");  // 表名作为三级标题，并带编号
                writer.write("| 名称 | 类型 | 注释 |\n");
                writer.write("| ---- | ---- | ---- |\n");

                exportTableStructureToMarkdown(writer, connection, tableName);
                writer.write("\n");  // 分隔每个表的结构

                tableIndex++;  // 增加表的索引
            }

            System.out.println("Markdown file created successfully!");

        } catch (SQLException | IOException e) {
            e.printStackTrace();
        }
    }

    public static void exportTableStructureToMarkdown(BufferedWriter writer, Connection connection, String tableName) {
        // 查询表结构
        String query = "SELECT column_name, data_type, column_comment " +
                "FROM information_schema.columns WHERE table_schema = 'edu_luohu' AND table_name = ?";

        try (PreparedStatement preparedStatement = connection.prepareStatement(query)) {
            preparedStatement.setString(1, tableName);

            try (ResultSet resultSet = preparedStatement.executeQuery()) {
                int columnIndex = 1;  // 列的索引
                while (resultSet.next()) {
                    String columnName = resultSet.getString("column_name");
                    String dataType = resultSet.getString("data_type");
                    String columnComment = resultSet.getString("column_comment");

                    // 写入 Markdown 表格内容，列前带编号
                    writer.write("| " + columnIndex + ". " + columnName + " | " + dataType + " | " + columnComment + " |\n");
                    columnIndex++;  // 增加列的索引
                }
            }
        } catch (SQLException | IOException e) {
            e.printStackTrace();
        }
    }
}
